package com.css.bjdt_core.validation.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.cell.service.CustomSqlService;
import com.css.bjdt_core.executive.report.ReportRequest;
import com.css.bjdt_core.reportFill.service.ReportFillService;
import com.css.bjdt_core.reportPrepare.entity.RptBatchTemplateRel;
import com.css.bjdt_core.reportPrepare.service.RptDealWithService;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.ReportConstant;
import com.css.bjdt_core.validation.entity.ValidationInfo;

@Service
public class ValidationInfoService extends CustomSqlService {
	@Autowired
	private Dao dao;
	@Autowired
	private RptDealWithService rptDealWithService;
	@Autowired
	private ReportValidateService reportValidateService;
	@Autowired
	private ReportFillService reportFillService;

	/**
	 * 按年度和党组织获取相关校核信息数据
	 * @param annualId 年度id
	 * @param dzzId 党组织id
	 * @return
	 */
	public List<ValidationInfo> getValidationInfoByTemplateId(String annualId, String dzzId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		return executeSql(ReportConstant.VALIDATION_QUERY_SQL, new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<ValidationInfo> list = new ArrayList<ValidationInfo>();
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setBatchId(rs.getString("BATCH_ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfo.setValidationState(rs.getString("VALIDATION_STATE"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setDzzId(rs.getString("DZZ_ID"));
					list.add(validationInfo);
				}
				return list;
			}
		}, new VarParam("table_name", tableName, "DZZ_ID", dzzId)).getList(ValidationInfo.class);
	}

	/**
	 * 删除
	 * @param annualId 年度id
	 * @param dzzId 党组织id
	 */
	public void deleteValidationInfo(String annualId, String dzzId, String templateId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		List<VarParam> varparams = new ArrayList<VarParam>();
		VarParam dzzIdParam = new VarParam(null, null, "dzz_id", dzzId);
		VarParam templateIdParam = new VarParam(null, null, "template_id", templateId);
		VarParam tableNameVar = new VarParam("table_name", tableName, null, null);
		varparams.add(dzzIdParam);
		varparams.add(templateIdParam);
		varparams.add(tableNameVar);
		executeSql(ReportConstant.VALIDATION_DELETE_SQL, null, varparams.toArray(new VarParam[0]));
	}

	/**
	 * 删除这个党组织下的所有的校核信息
	 * @param annualId 年度id
	 * @param dzzId 党组织id
	 */
	public void deleteValidationInfoByDzzId(String annualId, String dzzId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		List<VarParam> varparams = new ArrayList<VarParam>();
		VarParam dzzIdParam = new VarParam(null, null, "dzz_id", dzzId);
		VarParam tableNameVar = new VarParam("table_name", tableName, null, null);
		varparams.add(dzzIdParam);
		varparams.add(tableNameVar);
		executeSql(ReportConstant.VALIDATION_DELETE_SQL_DZZID, null, varparams.toArray(new VarParam[0]));
	}

	/**
	 * 校核报表数据
	 * @param
	 */
	public void deleteSubmitInformationValidationInfoByDzzId(String annualId, String dzzId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "delete from " + tableName + " where DZZ_ID = @dzzId and VALIDATION_TYPE in ('5','6')";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
	}

	public Map<String, Map<Integer, Integer>> selectValidationCount(String annualId, String dzzId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select count(1) COUNT, TEMPLATE_ID, VALIDATION_TYPE from (select DZZ_ID, TEMPLATE_ID, " +
				" (case when VALIDATION_TYPE in ('1','2','3') then 1 " +
				" when VALIDATION_TYPE = '4' then 2 " +
				" when VALIDATION_TYPE in ('5','6') then 3 " +
				" else null end) VALIDATION_TYPE " +
				" from "+ tableName +" where VALIDATION_STATE = '2' " +
				" and DZZ_ID = @dzzId) " +
				" where VALIDATION_TYPE is not null " +
				" group by TEMPLATE_ID, VALIDATION_TYPE ";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				Map<String, Map<Integer, Integer>> res = new HashMap<>();
				while (rs.next()) {
					String tid = rs.getString("TEMPLATE_ID");
					Integer type = rs.getInt("VALIDATION_TYPE");
					Integer count = rs.getInt("COUNT");


					if(res.containsKey(tid)) {
						Map<Integer, Integer> typeMap = res.get(tid);
						typeMap.put(type, count);
					} else {
						Map<Integer, Integer> typeMap = new HashMap<>();
						typeMap.put(type,count);
						res.put(tid, typeMap);
					}
				}
				return res;
			}
		});
		dao.execute(sql);
		return sql.getObject(Map.class);
	}

	/**
	 * 根据TYPE属性查看报表的校核错误条数
	 * @param
	 */
	public int selectValidationCountByType(String annualId, String dzzId, String templateId, List<String> typeArray) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String typeStr = CommonUtil.toString(typeArray, ",");
		String strSql = "select count(1) from " + tableName
				+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templatId and VALIDATION_STATE = '2' and VALIDATION_TYPE in ("
				+ typeStr + ")";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId).set("templatId", templateId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class);
	}

	/**
	 * 查询报表说明和报表批注校核未通过的条数
	 * @param annualId
	 * @param dzzId
	 */
	/**
	 * 校核报表数据
	 * @param
	 */
	public String reportExplainAndCommonValidationNum(String annualId, String dzzId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select count(1) from " + tableName
				+ " where DZZ_ID = @dzzId and VALIDATION_TYPE in ('5','6') and VALIDATION_STATE = '2'";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		Integer valCount = sql.getObject(Integer.class);
		return valCount + "";
	}

	/**
	 * 查询一张报表的校核错误条数
	 * @param
	 */
	public Integer reportValidationNum(String annualId, String dzzId, String templateId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select count(1) from " + tableName
				+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and VALIDATION_STATE = '2'";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.params().set("templateId", templateId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		Integer valCount = sql.getObject(Integer.class);
		return valCount;
	}

	/**
	 * 批量插入
	 * @param annualId 年度id
	 * @param validations 校核信息数据集合
	 */
	public void batchInsertValidationInfo(String annualId, List<ValidationInfo> validations) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		if (validations != null && validations.size() > 0) {
			List<List<ValidationInfo>> lists = CommonUtil.subList(validations, 70);
			List<Sql> sqls = new ArrayList<Sql>();

			for (List<ValidationInfo> list : lists) {
				StringBuilder batchInsertCellValueSql = new StringBuilder();
				batchInsertCellValueSql.append("INSERT ALL ");
				for (ValidationInfo validation : list) {
					batchInsertCellValueSql.append("INTO ").append(tableName).append(" VALUES (")
							.append(addQuotesStr(CommonUtil.getUUID())).append(",")
							.append(addQuotesStr(validation.getBatchId())).append(",")
							.append(addQuotesStr(validation.getSourceExp())).append(",")
							.append(addQuotesStr(validation.getCellExp())).append(",")
							.append(addQuotesStr(validation.getActualExp())).append(",")
							.append(addQuotesStr(validation.getValidationType())).append(",")
							.append(addQuotesStr(validation.getValidationState())).append(",")
							.append(addQuotesStr(validation.getCellId())).append(",")
							.append(addQuotesStr(validation.getDzzId())).append(",")
							.append(addQuotesStr(validation.getTemplateId())).append(")  ");
				}
				batchInsertCellValueSql.append(" SELECT * FROM dual");
				try {
					Sql batchInsertsql = Sqls.create(batchInsertCellValueSql.toString());
					sqls.add(batchInsertsql);
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			dao.execute(sqls.toArray(new Sql[0]));
		}
	}

	public String addQuotesStr(String str) {
		return "'" + str + "'";
	}

	public void createTable(String annualId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String count = executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		if ("0".equals(count) || null == count) {
			executeSql(ReportConstant.VALIDATION_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//专门给保存按钮做的校核信息方法
	public Map saveCellAfterValidation(String annualId, String dzzId, String batchId, String templateId) {
		Map valueMap = new HashMap();
		//先删除这个党组织下的这张报表的所有校核信息
		deleteValidationInfo(annualId, dzzId, templateId);
		RptBatchTemplateRel rptBatchTemplateRel = rptDealWithService.getARptBatchTemplateRel(batchId, templateId);
		if (rptBatchTemplateRel.getExamineState().equals("0")) {
			valueMap.put("1", "0");
			return valueMap;
		} else {
			ReportRequest reportRequest = new ReportRequest();
			reportRequest.setAnnualId(annualId);
			List<String> templateIdList = new ArrayList<String>();
			templateIdList.add(templateId);
			reportRequest.setTemplateIds(templateIdList);
			reportRequest.setReportDzzId(dzzId);
			reportRequest.setValidationReportType("1");
			//先把报表的校核公式加入缓存中
			reportValidateService.checkReportData(reportRequest, false);
			List<ValidationInfo> validationList = reportFillService.getValidationInfoByTemplateIdType(annualId,
					templateId, dzzId);

			if (validationList.size() == 0) {
				reportFillService.updateReportValidationState(batchId, templateId, "3");
				ValidationInfo validationInfo = new ValidationInfo();
				validationList.add(validationInfo);
				valueMap.put("3", "3");
			} else {
				reportFillService.updateReportValidationState(batchId, templateId, "2");
				valueMap.put("3", "2");
			}
			valueMap.put("1", "1");
			valueMap.put("2", validationList);
			return valueMap;
		}
	}
}
